package cn.com.dashihui.web.service;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.jfinal.aop.Before;
import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.plugin.activerecord.tx.Tx;

import cn.com.dashihui.kit.ExcelExportUtil;
import cn.com.dashihui.kit.ExcelExportUtil.Pair;
import cn.com.dashihui.web.dao.Category;
import cn.com.dashihui.web.dao.DeatiImage;
import cn.com.dashihui.web.dao.DetailsImg;
import cn.com.dashihui.web.dao.GoodsBaseImages;
import cn.com.dashihui.web.dao.GoodsItem;
import cn.com.dashihui.web.dao.GoodsSaleGT;
import cn.com.dashihui.web.dao.GoodsSaleItem;
import cn.com.dashihui.web.dao.GoodsSaleOption;
import cn.com.dashihui.web.dao.GoodsSaleSpec;
import cn.com.dashihui.web.dao.GoodsSaleTemplate;
import cn.com.dashihui.web.dao.VideoGoods;

	public class VideoGoodsService {
		/**
		 * 查找出所有商品
		 * 2015-10-26
		 */
		public List<VideoGoods> findAllGoodsStore(){
			return VideoGoods.me().find("SELECT * FROM t_bus_goods_video ORDER BY id ASC");
		}

		/**
		 * 分页查找商品
		 * @param pageNum
		 * @param pageSize
		 */
		public Page<Record> findByPage(int pageNum,int pageSize,int sGoodId,String sGoodName,String sSellerId,String sStoreId,int state){
			StringBuffer sBuffer = new StringBuffer("FROM t_bus_goods_video AS A "
					+ "INNER JOIN t_bus_goods_store_rel F ON A.good_store = F.goodstore "
					+ "left JOIN t_dict_store AS C ON F.storeid= C.storeid "
					+ "left JOIN t_bus_seller AS B ON B.sellerId= C.sellerId "
					+ "left join t_bus_goods_toker T on T.goodsId=A.id "
					+ "LEFT JOIN t_dict_category E on A.categoryonid=E.categoryId where 1=1");
			List<Object> params = new ArrayList<Object>();
			if(sGoodId!=0){
				sBuffer.append(" AND A.id=?");
				params.add(sGoodId);
			}
			if (StrKit.notBlank(sGoodName)) {
				sBuffer.append(" AND A.name like ?");
				params.add("%"+sGoodName+"%");
			}
			if(!StrKit.isBlank(sSellerId)&& !"0".equals(sSellerId)){
				sBuffer.append(" AND B.sellerId in ('"+sSellerId+"')");
			}
			if(!StrKit.isBlank(sStoreId)&&!"0".equals(sStoreId)){
				sBuffer.append(" AND F.storeid in ('"+sStoreId+"')");
			}
			switch (state) {
				case 1:
					sBuffer.append(" AND A.pstate=1 and A.state=1");
					break;
				case 2:
					sBuffer.append(" AND A.pstate=1 and A.state=2");
					break;
				case 3:
					sBuffer.append(" AND A.pstate=1 and A.state=3");
					break;
				case 4:
					sBuffer.append(" AND A.pstate=2");
					break;
				case 5:
					sBuffer.append(" AND A.pstate=3");
					break;
				case 6:
					sBuffer.append(" AND A.pstate=1 and A.state=4");
					break;
			}

			sBuffer.append(" GROUP BY A.id ORDER BY A.createDate DESC");
			return Db.paginate(pageNum, pageSize, "SELECT A.*,B.sellerName,E.categoryName as oneName ,T.state as tState ", sBuffer.toString(), params.toArray());
		}
		/**
		 * 查找商品
		 */

		public VideoGoods findById(int id){
			return VideoGoods.me().findFirst("SELECT A.*,B.sellerName,D.title FROM t_bus_goods_video AS A INNER JOIN t_bus_goods_store_rel F ON A.good_store = F.goodstore "
					+ "inner JOIN t_dict_store AS C ON F.storeid= C.storeid inner JOIN t_bus_seller AS B ON B.sellerId= C.sellerId "
					+ " left join t_bus_goods_title D on D.titleCode=A.titleCord where A.id=?",id);
		}
		public List<DeatiImage> findDetailAllImages(String LunBoNum) {
			return DeatiImage.me().find("SELECT A.* FROM goods_details_images A WHERE A.LunBoNum=? ORDER BY A.orderNo",LunBoNum);
		}
		public int doCancel(String id,String reason, String checktime) {
			return Db.update("update t_bus_goods_video set state=4 ,checktime=?,reason=? where id=?",checktime,reason,id);
		}
		public int doAccept(String id, String checktime) {
			return Db.update("update t_bus_goods_video set state=2 ,checktime=?,uptime=? where id=?",checktime,checktime,id);
		}
		public boolean delete(VideoGoods goods) {
			return goods.delete();
		}
		public int tooDown(String id, String checktime) {
			return Db.update("update t_bus_goods_video set state=5 ,checktime=? where id=?",checktime,id);
		}
		public int doDown(String id, String downtime) {
			return Db.update("update t_bus_goods_video set state=3 ,pstate=1,downtime=? WHERE id=?",downtime,id);
		}
		public int doUp( String id,String createDate ) {
			return Db.update("update t_bus_goods_video set state=1 ,pstate=1 ,createDate=? WHERE id=?",createDate,id);
		}
		public Record findGoodDetailById(int id) {
				return Db.findFirst("select * from t_bus_goods_video where id=?",id);
		}
		public List<Record> getGoodsItem(String gooditem) {
			return Db.find("select * from t_bus_goods_item where goodItem=?",gooditem);
		}
		public List<Record> getStore(String good_store) {
			return Db.find("SELECT A.title,A.address,A.tel,A.title FROM t_dict_store A WHERE A.storeid IN (select storeid from t_bus_goods_store_rel where goodstore=?) and A.qstate=1 and A.state=2 ",good_store);
		}
		
		/**
		 * 商品规格
		 * @param gooditem
		 * @return
		 */
		public List<Record> getGoodSpec(int goodsId) {
			return Db.find("select * from t_bus_goods_spec where goodId=?",goodsId);
		}
		
		public List<Record> findExcelByPage(int sGoodId,String sGoodName, String sSellerId, String sStoreId, int state) {
			StringBuffer sBuffer = new StringBuffer(" SELECT A.*,B.sellerName,E.categoryName as oneName,T.state as tState FROM t_bus_goods_video AS A INNER JOIN t_bus_goods_store_rel F ON A.good_store = F.goodstore inner JOIN t_dict_store AS C ON F.storeid= C.storeid inner JOIN t_bus_seller AS B ON B.sellerId= C.sellerId left join t_bus_goods_toker T on T.goodsId=A.id LEFT JOIN t_dict_category E on A.categoryonid=E.categoryId where 1=1");
			List<Object> params = new ArrayList<Object>();
			if(sGoodId!=0){
				sBuffer.append(" AND A.id=?");
				params.add(sGoodId);
			}
			if (StrKit.notBlank(sGoodName)) {
				sBuffer.append(" AND A.name like ?");
				params.add("%"+sGoodName+"%");
			}
			if(!StrKit.isBlank(sSellerId)&& !"0".equals(sSellerId)){
				sBuffer.append(" AND B.sellerId in ('"+sSellerId+"')");
			}
			if(!StrKit.isBlank(sStoreId)&&!"0".equals(sStoreId)){
				sBuffer.append(" AND F.storeid in ('"+sStoreId+"')");
			}
			switch (state) {
				case 1:
					sBuffer.append(" AND A.pstate=1 and A.state=1");
					break;
				case 2:
					sBuffer.append(" AND A.pstate=1 and A.state=2");
					break;
				case 3:
					sBuffer.append(" AND A.pstate=1 and A.state=3");
					break;
				case 4:
					sBuffer.append(" AND A.pstate=2");
					break;
				case 5:
					sBuffer.append(" AND A.pstate=3");
					break;
			}
			
			sBuffer.append(" GROUP BY A.id ORDER BY A.createDate DESC");
			return Db.find( sBuffer.toString(), params.toArray());
		}
		 
		/**
		 * 导出数据
		 * @param response
		 * @param request
		 * @param listz 
		 * @param titles2 
		 * @param member
		 */
		public void export(HttpServletResponse response, HttpServletRequest request, List<Pair> titles, List<Record> listz) {
			ExcelExportUtil.exportByRecord(response, request, "商品上架列表", titles, listz);
		}
		/**
		 * 查询所有的一级category
		 * @param iid
		 * @return
		 */
		public List<Category> finOneCategory(){
			return Category.me().find("SELECT * FROM  t_dict_category  WHERE categoryType=1");
		}
		/**
		 * 查询所有的二级category
		 * @param iid
		 * @return
		 */
		public List<Category> findTwoCategory(){
			return Category.me().find("SELECT * FROM  t_dict_category  WHERE categoryType=2");
		}
		/**
		 * 查询所有的三级category
		 * @param iid
		 * @return
		 */
		public List<Category> findThreeCategory(){
			return Category.me().find("SELECT * FROM  t_dict_category  WHERE categoryType=3");
		}
		/**
		 * 查询所有的四级category
		 * @param iid
		 * @return
		 */
		public List<Category> findFourCategory(){
			return Category.me().find("SELECT * FROM  t_dict_category  WHERE categoryType=4");
		}
		public int deteleItem(String goodItem) {
			return Db.update("DELETE FROM t_bus_goods_item WHERE goodItem=?",goodItem);
		}
		public int detelepayType(int id) {
			return Db.update("update t_bus_goods_video set sellpoint=0,sellPrice=0  WHERE id=?",id);
		}
		public int detelestore(String goodstore) {
			return Db.update("DELETE FROM t_bus_goods_store_rel WHERE goodstore=?",goodstore);
		}
		public int detelezdrs(int id) {
			return Db.update("update t_bus_goods_video set zdrs=0 WHERE id=?",id);
		}
		public int detelejyrs(int id) {
			return Db.update("update t_bus_goods_video set jyrs=0 WHERE id=?",id);
		}
		public VideoGoods findByIdGoods(int id){
			return VideoGoods.me().findFirst("SELECT * FROM  t_bus_goods_video WHERE id=?",id);
		}
		/**
		 *修改商品
		 */
		public boolean updategood(VideoGoods goods){
			return goods.update();
		}
		/**
		 * 根据商品名字获取商品ID  按时间排序倒序第一个
		 * @param name
		 * @return
		 */
		public int findidByName(String name){
			return VideoGoods.me().findFirst("SELECT * FROM  t_bus_goods_video WHERE name=? order by createDate desc",name).get("id");
		}
		/**
		 * 增加商品
		 */
		public boolean add(VideoGoods goods){
			return goods.save();
		}
		/**
		 * 轮播图片排序
		 * @param sortMap
		 * @return
		 */
		public boolean sortImages(Map<String,String> sortMap){
			int batchSize = sortMap.size();
			List<String> sqlList = new ArrayList<String>();
			for(String id : sortMap.keySet()){
				sqlList.add("UPDATE t_bus_goods_base_images SET orderNo="+sortMap.get(id)+" WHERE id="+id);
			}
			int[] result = Db.batch(sqlList,batchSize);
			return result.length>0;
		}
		
		/**
		 * 详情图片排序
		 * @param sortMap
		 * @return
		 */
		public boolean sortDetailImages(Map<String,String> sortMap){
			int batchSize = sortMap.size();
			List<String> sqlList = new ArrayList<String>();
			for(String id : sortMap.keySet()){
				sqlList.add("UPDATE goods_details_images SET orderNo="+sortMap.get(id)+" WHERE id="+id);
			}
			int[] result = Db.batch(sqlList,batchSize);
			return result.length>0;
		}
		
		/**
		 * 商品图片列表
		 * 
		 * @return
		 */
		public List<GoodsBaseImages> findAllImages(String LunBoNum){
			return GoodsBaseImages.me().find("SELECT A.* FROM t_bus_goods_base_images A WHERE A.LunBoNum=? ORDER BY A.orderNo",LunBoNum);
		}
		
		/**
		 * 商品详情图片列表
		 * 
		 * @return
		 */
		public List<DetailsImg> findDetailImages(String LunBoNum){
			return DetailsImg.me().find("SELECT A.* FROM goods_details_images A WHERE A.LunBoNum=? ORDER BY A.orderNo",LunBoNum);
		}
		
		
		/**
		 * 添加图片
		 * @param newObject
		 * @return
		 */
		public boolean addImage(GoodsBaseImages newObject){
			return newObject.save();
		}
		
		
		/**
		 * 添加详情图片
		 * @param newObject
		 * @return
		 */
		public boolean addDetailImage(DetailsImg newObject){
			return newObject.save();
		}
		
		
		/**
		 * 删除图片
		 * @param id
		 * @return
		 */
		public boolean delImage(int id){
			return GoodsBaseImages.me().deleteById(id);
		}
		
		
		/**
		 * 删除详情图片
		 * @param id
		 * @return
		 */
		public boolean delDetailImage(int id){
			return DetailsImg.me().deleteById(id);
		}
		
		
		/**
		 * 设置图片为商品logo
		 * @param id
		 * @return
		 */
		@Before(Tx.class)
		public boolean setImageLogo(int goodsid,int id){
			List<String> sqlList = new ArrayList<String>();
			//更新该商品所有LOGO的isLogo标识为0
			sqlList.add("UPDATE t_bus_goods_base_images SET isLogo=0 WHERE goodsid="+goodsid);
			//更新当前LOGO的isLogo标识为1
			sqlList.add("UPDATE t_bus_goods_base_images SET isLogo=1 WHERE goodsid="+goodsid+" AND id="+id);
			//更新该商品的LOGO字段值为当前LOGO的地址
			sqlList.add("UPDATE t_bus_goods_base SET thumb=(SELECT thumb FROM t_bus_goods_base_images WHERE id="+id+") WHERE id="+goodsid);
			Db.batch(sqlList, sqlList.size());
			return true;
		}
		/**
		 * 
		 * 自定义参数存储
		 * @param option
		 * @return
		 */
		public boolean saveItem(GoodsSaleItem goodsSaleItem){
			return goodsSaleItem.save();
		}
		/**
		 * 删除当前商品对应的扩展信息
		 * @param goodId
		 * @return
		 */
		public int deleteitem(int goodsid) {
			return Db.update("DELETE FROM t_bus_goods_sale_items WHERE goodsid=?",goodsid);
		}
		
		/**
		 * 根据商品id查询所有的扩展信息
		 * @param iid
		 * @return
		 */
		public List<GoodsItem> findAllItems(int goodsid){
			return GoodsItem.me().find("SELECT * FROM  t_bus_goods_sale_items  WHERE goodsid=?",goodsid);
		}
		
		/**
		 * 查询对应的二级category
		 * @param iid
		 * @return
		 */
		public List<Record> finTwoCategory(String categoryonid) {
			return Db.find("select categoryId,categoryName from t_dict_category where categoryFatherId in ('"+categoryonid+"')");
		}
		/**
		 * 查询对应的三级category
		 * @param iid
		 * @return
		 */
		public List<Record> finThreeCategory(String categorytwid) {
			return Db.find("select categoryId,categoryName from t_dict_category where categoryFatherId in ('"+categorytwid+"')");
		}
		/**
		 * 查询对应的四级category
		 * @param iid
		 * @return
		 */
		public List<Record> finFourCategory(String categorythid) {
			return Db.find("select categoryId,categoryName from t_dict_category where categoryFatherId in ('"+categorythid+"')");
		}
		/**
		 * 查询所有的模板信息
		 */
		public List<GoodsSaleTemplate> findAllGoodsTemplates(){
			return GoodsSaleTemplate.me().find("SELECT * FROM t_bus_goods_sale_template");
		}
		/**
		 * 存储商品模板关联信息
		 */
		public boolean savegt(GoodsSaleGT goods){
			return goods.save();
		}
		/**
		 * 查询当前商品是否已关联模板
		 */
		public GoodsSaleGT findTemBygoodsid(int goodsid){
			return GoodsSaleGT.me().findFirst("SELECT * FROM t_bus_goods_sale_gt WHERE goodsid=?" ,goodsid);
		}
		/**
		 * 根据模板id查询模板信息
		 * @param tid
		 * @return
		 */
		public GoodsSaleTemplate findTemplateById(int tid) {
			// TODO Auto-generated method stub
			return GoodsSaleTemplate.me().findFirst("SELECT * FROM t_bus_goods_sale_template WHERE id = ?",tid);
		}
		/**
		 * 根据规格id查询对应option
		 * @param specid
		 * @param id 
		 * @return 
		 */
		/*public  List<Option> finOptionById(int specid, int id) {
			// TODO Auto-generated method stub
			return Option.me().find("SELECT id FROM t_bus_goods_sale_option WHERE specid = ?",specid).get(id);
		}*/
		public List<GoodsSaleOption> finOptionById(Integer specid){
			return GoodsSaleOption.me().find("SELECT * FROM  t_bus_goods_sale_option o WHERE specid=?",specid);
		}
		/**
		 * 根据id查询规格名称
		 * @param iid
		 * @return
		 */
		public GoodsSaleSpec findSpecByid(int specid) {
			// TODO Auto-generated method stub
			return GoodsSaleSpec.me().findFirst("SELECT * FROM t_bus_goods_sale_spec WHERE id = ?",specid);
		}
		/**
		 * 根据id查询参数名称
		 * @param id
		 * @return
		 */
		public String findOptionNameByid(int id){
			return Category.me().findFirst("SELECT o.optionName FROM  t_bus_goods_sale_option o WHERE id=?",id).get("optionName");
		}	
}